﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web.Configuration;

namespace EmployeeCheckIn
{
    public partial class ShowAddtime : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Session["User"] == null && Session["Pass"] == null)
                {
                    Response.Redirect("Default.aspx");
                }

            }
        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList2.SelectedValue = DropDownList1.SelectedValue;
        }

        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList1.SelectedValue = DropDownList2.SelectedValue;
        }

        public void cleartext()
        {

            TextBox2.Text = "";
            txt_InTime.Text = "";
            txt_OutTime.Text = "";
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if (DropDownList1.SelectedValue == "" || DropDownList2.SelectedValue == "" || TextBox2.Text == "" || txt_InTime.Text == "" || txt_OutTime.Text == "")
            {
                lbwarn.Text = "กรุณากรอกข้อมูลให้ครบ";
            }
            
            else
            {
                SerchDB timest = new SerchDB();
                CutString cuttime = new CutString();
                InSertData InsertTime = new InSertData();
                DataTable dt = timest.timestart();
                EditTimeUpdate latetime = new EditTimeUpdate();
                DateTime StartTime = cuttime.dtm(TextBox2.Text, dt.Rows[0]["TIME_OF_START"].ToString());
                DateTime CheckIn = cuttime.dtm(TextBox2.Text, txt_InTime.Text);
                DateTime CheckOut = cuttime.dtm(TextBox2.Text, txt_OutTime.Text);
                if ((CheckIn.Minute == 0 && CheckIn.Hour == 0)||(CheckOut.Minute == 0 && CheckOut.Hour == 0))
                {
                    lbwarn.Text = "ตรวจสอบรูปแบบเวลา HH:mm และห้ามใส่ 00:00";
                }
                else
                {
                    int LateTime = latetime.ReLateTime(CheckIn, StartTime);
                    InsertTime.InsertTime(Convert.ToInt32(DropDownList1.SelectedValue), CheckIn, CheckOut, LateTime);
                    cleartext();
                    lbwarn.Text = "เพิ่มข้อมูลสำเร็จ";
                }
            }
        }

        private void Excel_import_2003(string s)
        {
            //s = Server.MapPath("~/Excelfile/") + "example2.xls";
            //Declare Variables - Edit these based on your particular situation
            string sSQLTable = "TBL_IM_PORT";
            // make sure your sheet name is correct, here sheet name is Sheet1, so you can change your sheet name if have different
            string myExcelDataQuery = "Select EMP_CODE,DATE_TIME,CHECK_IN,CHECK_OUT from [Sheet1$]";
            //Create our connection strings
            string sExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + s + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            string connectionString = WebConfigurationManager.ConnectionStrings["DB_CHECK_EMPLOYEEConnectionString2"].ConnectionString;
            try
            {
                using (OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString))
                {
                    OleDbConn.Open();
                    using (OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn))
                    {
                        OleDbDataReader dr = OleDbCmd.ExecuteReader();
                        SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
                        bulkCopy.DestinationTableName = sSQLTable;
                        do
                        {
                            bulkCopy.WriteToServer(dr);
                        }
                        while (dr.Read());
                        {
                            bulkCopy.WriteToServer(dr);
                        }
                        lbwarn.Text = "เพิ่มข้อมูลสำเร็จ";
                    }
                }

            }
            catch (Exception e)
            {
                lbwarn.Text = e.Message;
            }
        }
        protected void BtUpload_Click(object sender, EventArgs e)
        {
            String path = Server.MapPath("~/App_Data/") + FileUpload_Excel.FileName;
            FileUpload_Excel.SaveAs(path);
            Excel_import_2003(path);
            ImportExcal();
            System.IO.File.Delete(path + FileUpload_Excel.FileName);
        }
        private void ImportExcal()
        {
            SerchDB Imp = new SerchDB();
            CutString cutdate = new CutString();
            Imp.ClearNull();
            DataTable TBImp = Imp.Fromimport();
            DataTable dt = Imp.timestart();
            EditTimeUpdate latetime = new EditTimeUpdate();
            InSertData InsertTime = new InSertData();
            for (int i = 0; i < TBImp.Rows.Count; i++)
            {
                int EMPID = Convert.ToInt32(TBImp.Rows[i]["EMP_ID"]);
                DateTime StartTime = cutdate.dtm(Convert.ToDateTime(TBImp.Rows[i]["DateTime_IP"].ToString()).ToString("dd/MM/yyyy"), dt.Rows[0]["TIME_OF_START"].ToString());
                DateTime CheckIn = cutdate.dtm(Convert.ToDateTime(TBImp.Rows[i]["DateTime_IP"].ToString()).ToString("dd/MM/yyyy"), TBImp.Rows[i]["CHECKIN"].ToString());
                DateTime CheckOut = cutdate.dtm(Convert.ToDateTime(TBImp.Rows[i]["DateTime_IP"].ToString()).ToString("dd/MM/yyyy"), TBImp.Rows[i]["CHECKOUT"].ToString());
                if ((CheckIn.Minute == 0 && CheckIn.Hour == 0)||(CheckOut.Minute == 0 && CheckOut.Hour == 0))
                {
                    lbwarn.Text = "ตรวจสอบรูปแบบเวลา HH:mm และห้ามใส่ 00:00";
                }
                else
                {
                    int LateTime = latetime.ReLateTime(CheckIn, StartTime);
                    InsertTime.InsertTime(EMPID, CheckIn, CheckOut, LateTime);
                }
            }
            Imp.ClearImport();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            Response.AppendHeader("Content-Disposition", "attachment; filename=example.xls");
            Response.TransmitFile(Server.MapPath("~/Excelfile/example.xls"));
            Response.End();
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            Response.Redirect("ShowTime_Empoyee.aspx");
        }

    }
}